package com.renjun.smweb.commons.util;

import com.renjun.smweb.commons.global.DataMap;
import com.renjun.smweb.exceptions.BaseException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Rio-Lee on 2016/7/3.
 * 导出Excel工具
 */
public class ExportExcel {

    private static final String JSON_STR = "\\{(\\w+:.+)(,\\w+:.+)*\\}";

    private Workbook workbook;

    private String filePath;

    private Map<String, String> dataHeadMap = new DataMap<>();

    private Map<String,String> linkHeadMap = new LinkedHashMap<>();

    private Map<String, Integer> headIndexMap = new DataMap<>();

    private List<Map<String, Object>> currentData = new ArrayList<>();

    public ExportExcel() {
    }

    private String searchKeyByValue(String val) {
        for (Map.Entry<String, String> data : dataHeadMap.entrySet()) {
            if (data.getValue().equals(val)) return data.getKey();
        }
        return null;
    }

    private String searchKeyByIndex(int i) {
        for (Map.Entry<String, Integer> entry : headIndexMap.entrySet()) {
            if (i == entry.getValue()) return entry.getKey();
        }
        return null;
    }

    public List<Map<String, Object>> readExcel() throws IOException, BaseException {
        List<Map<String, Object>> result = new ArrayList<>();
        Workbook read;
        if (filePath.toLowerCase().endsWith(".xlsx")) {
//            read = new HSSFWorkbook(new FileInputStream(filePath));
            read = new XSSFWorkbook(new FileInputStream(filePath));
        } else if (filePath.toLowerCase().endsWith("xlsx")) {
            read = new XSSFWorkbook(new FileInputStream(filePath));
        } else {
            throw new IOException();
        }
        Sheet sheet = read.getSheetAt(0);
        Row headRow = sheet.getRow(0);
        String[] heads = new String[headRow.getLastCellNum()];

        //检查模版是否正确
        if(dataHeadMap.size()!=heads.length){
            throw new BaseException("导入模板样式错误");
        }
        Row row0=sheet.getRow(0);
        int colNum=0;
        for (Map.Entry<String, String> entry : linkHeadMap.entrySet()) {
            String colValue = row0.getCell(colNum).getStringCellValue().trim();
            colNum++;
            if (!colValue.equals(entry.getValue())){
                throw new BaseException("导入模板样式错误");
            }
        }
        for (int i = 0, l = headRow.getLastCellNum(); i < l; i++) {
            String ch = headRow.getCell(i).getStringCellValue();
//            String en = searchKeyByValue(ch);
            String en = searchKeyByIndex(i);
            heads[i] = en == null ? String.valueOf(i) : en;
        }
        Integer count = -4;//记录空行数 2017年3月24日 09:17:15
        for (int row = 1, len = sheet.getLastRowNum(); row <= len; row++) {
            Map<String, Object> item = new DataMap<>();
            Row currentRow = sheet.getRow(row);
            if (currentRow == null){
                if ( 0 < count ) {
                    break;
                }
                count++;
                continue;
            }
//            if (currentRow == null) continue;

            Cell tempCell0 = currentRow.getCell(0);
            if(tempCell0!=null)currentRow.getCell(0).setCellType(XSSFCell.CELL_TYPE_STRING);
            Cell tempCell1 = currentRow.getCell(1);
            if(tempCell1!=null)currentRow.getCell(1).setCellType(XSSFCell.CELL_TYPE_STRING);
            Cell tempCell2 = currentRow.getCell(2);
            if(tempCell2!=null)currentRow.getCell(2).setCellType(XSSFCell.CELL_TYPE_STRING);
            if ((tempCell0 == null || "".equals(tempCell0.getStringCellValue())) &&
                    (tempCell1 == null || "".equals(tempCell1.getStringCellValue()) &&
                            (tempCell2 == null || "".equals(tempCell2.getStringCellValue())))) {
                if ( 0 < count ) {
                    break;
                }
                count++;
                continue;
            } else {
                count = -4;
            }

            for (int col = 0, last = heads.length; col < last; col++) {
                Cell currentCell = currentRow.getCell(col);
                if (currentCell == null) {
                    String currKey = heads[col];
                    item.put(currKey, null);
                    continue;
                }
                currentRow.getCell(col).setCellType(XSSFCell.CELL_TYPE_STRING);
                String currValue = currentRow.getCell(col).getStringCellValue().trim();
                String currKey = heads[col];
                item.put(currKey, currValue);
            }
            result.add(item);
        }
        return result;
    }


    public void writeCurrentData() throws IOException {
        flushCurrentDataToWorkbook();
        int index = filePath.lastIndexOf("/");
        File dir = new File(filePath.substring(0, index));
        if (!dir.exists()) dir.mkdirs();
        FileOutputStream fos = new FileOutputStream(filePath);
        workbook.write(fos);
        fos.close();
    }


    public void setCurrentData(List<Map<String, Object>> datas) {
        currentData.clear();
        for (Map<String, Object> data : datas) {
            Map<String, Object> tempCurrentItem = new DataMap<>();
            for (Map.Entry<String, Integer> headIndex : headIndexMap.entrySet()) {
                String key = headIndex.getKey();
                Integer idx = headIndex.getValue();
                Object value = data.get(key);
                tempCurrentItem.put(idx.toString(), value == null ? "" : value);
            }
            currentData.add(tempCurrentItem);
        }
    }

    private void flushCurrentDataToWorkbook() {
        Sheet sheet = workbook.createSheet();
        Row headRow = sheet.createRow(0);
        for (Map.Entry<String, String> head : dataHeadMap.entrySet()) {
            Integer cellIndex = headIndexMap.get(head.getKey());
            if (cellIndex != null) {
                Cell headCell = headRow.createCell(cellIndex);
                headCell.setCellType(Cell.CELL_TYPE_STRING);
                headCell.setCellValue(head.getValue());
            }
        }
        for (int row = 0, len = currentData.size(); row < len; row++) {
            Row contentRow = sheet.createRow(row + 1);
            for (Map.Entry<String, Object> curr : currentData.get(row).entrySet()) {
                int cellIndex = Integer.valueOf(curr.getKey());
                String val = curr.getValue() == null ? "" : curr.getValue().toString().trim();
                Cell contentCell = contentRow.createCell(cellIndex);
                contentCell.setCellValue(val);
            }
        }
    }

    private ExportExcel(String filePath, String headConfig) throws IOException {
        if (filePath != null && filePath.toLowerCase().endsWith("xlsx")) {
            this.workbook = new XSSFWorkbook();
        } else if (filePath != null && filePath.toLowerCase().endsWith(".xlsx")) {
//            this.workbook = new HSSFWorkbook();
            this.workbook = new XSSFWorkbook();
        } else {
            throw new IOException();
        }
        this.filePath = filePath;
        if (headConfig != null && headConfig.matches(JSON_STR)) {
            headConfig = headConfig.substring(1, headConfig.length() - 1);
            String[] items = headConfig.split(",");
            for (int i = 0, len = items.length; i < len; i++) {
                String ch = items[i].split(":")[1];
                String en = items[i].split(":")[0];
                headIndexMap.put(en, i);
                dataHeadMap.put(en, ch);
                linkHeadMap.put(en, ch);
            }
        }
    }

    public static ExportExcel createExportExcel(String filePath, String headConfig) throws IOException {
        return new ExportExcel(filePath, headConfig);
    }


    public void createExportExcel(String filePath) throws IOException {
        if (filePath != null && filePath.toLowerCase().endsWith("xlsx")) {
            this.workbook = new XSSFWorkbook();
        } else if (filePath != null && filePath.toLowerCase().endsWith(".xlsx")) {
//            this.workbook = new HSSFWorkbook();
            this.workbook = new XSSFWorkbook();
        } else {
            throw new IOException();
        }
        this.filePath = filePath;
    }

    /**
     * 生成待统筹学生上报表M7-4
     *
     * @throws IOException
     */
    public void writeExpDaiTongchouSbReport(String schoolname) throws IOException {
        expDaiTongchouSbReportMain(schoolname);
        int index = filePath.lastIndexOf("/");
        File dir = new File(filePath.substring(0, index));
        if (!dir.exists()) dir.mkdirs();
        FileOutputStream fos = new FileOutputStream(filePath);
        workbook.write(fos);
        fos.close();
    }

    /**
     * 生成待统筹学生上报表M7-4
     */
    private void expDaiTongchouSbReportMain(String schoolname) {
        String title = schoolname + "待统筹学生上报表";
        String tail = "表格生成时间：" + Format.getDateTime() + "\n" +
                "\n" +
                "经办人员（签字）：\n" +
                "校长（签字）：\n" +
                "学校公章：";

        Sheet sheet = workbook.createSheet();
        workbook.setSheetName(0, "统筹上报");
        sheet.setColumnWidth(0, 100 * 15);
        sheet.setColumnWidth(1, 250 * 15);
        sheet.setColumnWidth(2, 100 * 15);
        sheet.setColumnWidth(3, 300 * 15);
        sheet.setColumnWidth(4, 350 * 15);
        sheet.setColumnWidth(5, 250 * 15);
        sheet.setColumnWidth(6, 100 * 15);
        int totalColumnNum = headIndexMap.size() - 1;
        Row firstRow = sheet.createRow(0);
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 6);
        Cell cell_1 = firstRow.createCell(0);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        //指定列宽


        Font fontds = workbook.createFont();
        fontds.setFontHeightInPoints((short) 14);//字体大小
        fontds.setFontName("宋体");
        fontds.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体
        cellStyle.setFont(fontds);

        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        firstRow.setHeightInPoints(40);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);


        cell_1.setCellStyle(cellStyle);
        cell_1.setCellValue(title);
        sheet.addMergedRegion(cra);

        Cell cell_7 = firstRow.createCell(6);
        cell_7.setCellStyle(cellStyle);
        cell_7.setCellValue("");

        Row headRow = sheet.createRow(1);
        for (Map.Entry<String, String> head : dataHeadMap.entrySet()) {
            Integer cellIndex = headIndexMap.get(head.getKey());
            if (cellIndex != null) {
                //颜色特技
                Cell headCell = headRow.createCell(cellIndex);
                CellStyle cellStyle3 = workbook.createCellStyle();
                Font font1 = workbook.createFont();
                font1.setFontName("宋体");
                font1.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体
                cellStyle3.setFont(font1);
//                cellStyle3.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE
//                        .getIndex());
//                cellStyle3.setFillPattern(CellStyle.SOLID_FOREGROUND);
                cellStyle3.setBorderBottom(CellStyle.BORDER_THIN);
                cellStyle3.setBorderTop(CellStyle.BORDER_THIN);
                cellStyle3.setBorderLeft(CellStyle.BORDER_THIN);
                cellStyle3.setBorderRight(CellStyle.BORDER_THIN);

                headCell.setCellStyle(cellStyle3);
                headCell.setCellValue(head.getValue());
            }
        }
        // write data
        for (int row = 0, len = currentData.size(); row < len; row++) {
            Row contentRow = sheet.createRow(row + 2);
            for (Map.Entry<String, Object> curr : currentData.get(row).entrySet()) {
                int cellIndex = Integer.valueOf(curr.getKey());
                String val = curr.getValue() == null ? "" : curr.getValue().toString().trim();
                Cell contentCell = contentRow.createCell(cellIndex);
                CellStyle cellStyleis = workbook.createCellStyle();
                cellStyleis.setBorderBottom(CellStyle.BORDER_THIN);
                cellStyleis.setBorderTop(CellStyle.BORDER_THIN);
                cellStyleis.setBorderLeft(CellStyle.BORDER_THIN);
                cellStyleis.setBorderRight(CellStyle.BORDER_THIN);
                contentCell.setCellStyle(cellStyleis);

                contentCell.setCellValue(val);
            }
        }
        //在sheet里增加合并单元格
        /*  合并所有列
            设置字体
            写入最后一行数据*/
        Row tailRow = sheet.createRow(currentData.size() + 2);
        tailRow.setHeightInPoints(80);
        CellRangeAddress cra1 = new CellRangeAddress(currentData.size() + 2, currentData.size() + 2, 0, totalColumnNum);

        sheet.addMergedRegion(cra1);
        Cell cell_tail1 = tailRow.createCell(0);
        cell_tail1.setCellValue(tail);//最后一行
        //加大字体
        CellStyle cellStyle2 = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 11);//字体大小
        font.setFontName("宋体");
        cellStyle2.setFont(font);
        cellStyle2.setWrapText(true);
        cellStyle2.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle2.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle2.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle2.setBorderRight(CellStyle.BORDER_THIN);
        cell_tail1.setCellStyle(cellStyle2);
        for (int i = 1; i <= 6; i++) {
            Cell cell_tail = tailRow.createCell(i);
            cell_tail.setCellStyle(cellStyle2);
            cell_tail.setCellValue("");
        }


    }


    /**
     * 招生录取统计统一格式
     *
     * @param resultList 在籍学生集合
     * @throws IOException
     */
    public void writeCurrentDataZslq(List<Map<String, Object>> resultList, String sheetName) throws IOException {

        String sheetTitle = "{XULIE:序号,SCHOOLNAME:学校名称,BJZS:班级总数,YEZS:幼儿总数,BSYERS:幼儿人数,BSZB:占%,FBSYERS:幼儿人数,FBSZB:占%,FBSHJZS:上海市居住证+房产证,FBSHJLS:上海市临时居住证/灵活证,FBSHJQT:其它,NANZS:男生总数,NVZS:女生总数,GA:港澳,TW:台湾,WHJ:无户籍,TSETS:特殊儿童数,BZ:备注}";
        flushCurrentZslqDataToWorkbook(0, sheetName, sheetTitle, resultList);
        int index = filePath.lastIndexOf("/");
        File dir = new File(filePath.substring(0, index));
        if (!dir.exists()) dir.mkdirs();
        FileOutputStream fos = new FileOutputStream(filePath);
        workbook.write(fos);
        fos.close();
    }


    /**
     * 招生录取统计统一格式
     *
     * @param sheetNum   sheet页
     * @param sheetName  sheet名称
     * @param headConfig 表头
     * @param result     当前数据集
     */
    private void flushCurrentZslqDataToWorkbook(int sheetNum, String sheetName, String headConfig, List<Map<String, Object>> result) {
        headIndexMap.clear();
        currentData.clear();
        dataHeadMap.clear();

        // 生成一个表格
        Sheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetName);
        // 设置表格默认列宽度为8个字节
        sheet.setDefaultColumnWidth((short) 8);
        // 设置第一列宽度
        sheet.setColumnWidth(1, 9000);
        // 设置第八列宽度
        sheet.setColumnWidth(8, 3500);
        // 设置第九列宽度
        sheet.setColumnWidth(9, 3500);
        // 冻结第一行和第二行
        sheet.createFreezePane(0, 3, 0, 3);
        //定义表格样式
        CellStyle style = workbook.createCellStyle();
        //表格边框
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        //表格水平居中
        style.setAlignment(CellStyle.ALIGN_CENTER);
        // 表头背景色
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //表头垂直居中
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        //自动换行
        style.setWrapText(true);


        //定义表格表头样式
        CellStyle headStyle = workbook.createCellStyle();
        //表格边框
        headStyle.setBorderBottom(CellStyle.BORDER_THIN);
        headStyle.setBorderTop(CellStyle.BORDER_THIN);
        headStyle.setBorderRight(CellStyle.BORDER_THIN);
        headStyle.setBorderLeft(CellStyle.BORDER_THIN);
        //表格水平居中
        headStyle.setAlignment(CellStyle.ALIGN_CENTER);
        // 表头背景色
        headStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
        headStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //表头垂直居中
        headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        //自动换行
        headStyle.setWrapText(true);

        //第一行、第二行表头合并居中
        headMergedRegionZslq(sheet, result, sheetName);

        //第三行表头开始
        Row headRow1 = sheet.createRow(2);
        //设置第三行
        sheet.getRow(2).setHeight((short) 600);


        //表头处理
        if (headConfig != null && headConfig.matches(JSON_STR)) {
            headConfig = headConfig.substring(1, headConfig.length() - 1);
            String[] items = headConfig.split(",");
            for (int i = 0, len = items.length; i < len; i++) {
                String ch = items[i].split(":")[1];
                String en = items[i].split(":")[0];
                headIndexMap.put(en, i);
                dataHeadMap.put(en, ch);
            }
        }

        //当前结果集处理
        int i = 1;
        for (Map<String, Object> data : result) {
            Map<String, Object> tempCurrentItem = new DataMap<>();
            for (Map.Entry<String, Integer> headIndex : headIndexMap.entrySet()) {
                String key = headIndex.getKey();
                key = key.toUpperCase();
                Integer idx = headIndex.getValue();
                Object value = data.get(key);
                tempCurrentItem.put(idx.toString(), value == null ? "" : value);
            }
            tempCurrentItem.put("0", i++);
            currentData.add(tempCurrentItem);
        }

        // 产生表格标题行
        for (Map.Entry<String, String> head : dataHeadMap.entrySet()) {
            Integer cellIndex = headIndexMap.get(head.getKey());
            if (cellIndex != null) {
                Cell headCell = headRow1.createCell(cellIndex);
                headCell.setCellType(Cell.CELL_TYPE_STRING);
                headCell.setCellValue(head.getValue());
                headCell.setCellStyle(headStyle);

            }
        }
        // 遍历集合数据，产生数据行
        for (int row = 0, len = currentData.size(); row < len; row++) {
            Row contentRow = sheet.createRow(row + 3);
            for (Map.Entry<String, Object> curr : currentData.get(row).entrySet()) {
                int cellIndex = Integer.valueOf(curr.getKey());
                String val = curr.getValue() == null ? "" : curr.getValue().toString().trim();
                Cell contentCell = contentRow.createCell(cellIndex);
                contentCell.setCellValue(val);
                contentCell.setCellStyle(style);
                sheet.getRow(row + 3).setHeight((short) 600);
            }
        }
    }

    /**
     * 招生录取统计的表头样式
     */
    private void headMergedRegionZslq(Sheet sheet, List<Map<String, Object>> result, String sheetName) {
        CellStyle headerStyle = workbook.createCellStyle();
        //头部边框
        headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
        headerStyle.setBorderTop(CellStyle.BORDER_THIN);
        headerStyle.setBorderRight(CellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
        //表头水平居中
        headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        //表头垂直居中
        headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // 表头背景色
        headerStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
        headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //自动换行
        headerStyle.setWrapText(true);


        CellStyle headerStyle0 = workbook.createCellStyle();
        //头部边框
        headerStyle0.setBorderBottom(CellStyle.BORDER_THIN);
        headerStyle0.setBorderTop(CellStyle.BORDER_THIN);
        headerStyle0.setBorderRight(CellStyle.BORDER_THIN);
        headerStyle0.setBorderLeft(CellStyle.BORDER_THIN);
        //表头水平居中
        headerStyle0.setAlignment(CellStyle.ALIGN_CENTER);
        //表头垂直居中
        headerStyle0.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // 表头背景色
        headerStyle0.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
        headerStyle0.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //自动换行
        headerStyle0.setWrapText(true);
        //设置表头第一行字体样式
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 20);
        headerStyle0.setFont(font);


        //取得第一行
        Row headRow0 = sheet.createRow(0);
        //设置第一行
        sheet.getRow(0).setHeight((short) 1000);

        //第一行第一列
        Cell cell = headRow0.createCell(0);
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 17);
        sheet.addMergedRegion(cra);
        cell.setCellStyle(headerStyle0);
        cell.setCellValue(sheetName);


        //取得第二行
        headRow0 = sheet.createRow(1);
        //设置第二行
        sheet.getRow(1).setHeight((short) 600);

        //第二行第一列
        Cell cell0 = headRow0.createCell(0);
        CellRangeAddress cra0 = new CellRangeAddress(1, 2, 0, 0);
        sheet.addMergedRegion(cra0);
        cell0.setCellStyle(headerStyle);
        cell0.setCellValue("序号");

        Cell cell00 = headRow0.createCell(1);
        CellRangeAddress cra00 = new CellRangeAddress(1, 2, 1, 1);
        sheet.addMergedRegion(cra00);
        cell00.setCellStyle(headerStyle);
        cell00.setCellValue("学校名称");

        Cell cell1 = headRow0.createCell(2);
        cell1.setCellValue("班级总数");
        CellRangeAddress cra1 = new CellRangeAddress(1, 2, 2, 2);
        sheet.addMergedRegion(cra1);
        cell1.setCellStyle(headerStyle);

        Cell cell3 = headRow0.createCell(3);
        cell3.setCellValue("幼儿总数");
        CellRangeAddress cra2 = new CellRangeAddress(1, 2, 3, 3);
        sheet.addMergedRegion(cra2);
        cell3.setCellStyle(headerStyle);

        Cell cell5 = headRow0.createCell(4);
        cell5.setCellValue("本市");
        CellRangeAddress cra3 = new CellRangeAddress(1, 1, 4, 5);
        sheet.addMergedRegion(cra3);
        cell5.setCellStyle(headerStyle);

        Cell cell6 = headRow0.createCell(5);
        cell6.setCellStyle(headerStyle);

        Cell cell7 = headRow0.createCell(6);
        cell7.setCellValue("非本市");
        CellRangeAddress cra4 = new CellRangeAddress(1, 1, 6, 7);
        sheet.addMergedRegion(cra4);
        cell7.setCellStyle(headerStyle);

        Cell cell8 = headRow0.createCell(7);
        cell8.setCellStyle(headerStyle);

        Cell cell9 = headRow0.createCell(8);
        cell9.setCellValue("非本市户籍");
        CellRangeAddress cra5 = new CellRangeAddress(1, 1, 8, 10);
        sheet.addMergedRegion(cra5);
        cell9.setCellStyle(headerStyle);

        Cell cell10 = headRow0.createCell(9);
        cell10.setCellStyle(headerStyle);

        Cell cell11 = headRow0.createCell(10);
        cell11.setCellStyle(headerStyle);

        Cell cell12 = headRow0.createCell(11);
        cell12.setCellValue("男生总数");
        CellRangeAddress cra6 = new CellRangeAddress(1, 2, 11, 11);
        sheet.addMergedRegion(cra6);
        cell12.setCellStyle(headerStyle);

        Cell cell13 = headRow0.createCell(12);
        cell13.setCellValue("女生总数");
        CellRangeAddress cra7 = new CellRangeAddress(1, 2, 12, 12);
        sheet.addMergedRegion(cra7);
        cell13.setCellStyle(headerStyle);

        Cell cell14 = headRow0.createCell(13);
        cell14.setCellValue("港澳");
        CellRangeAddress cra8 = new CellRangeAddress(1, 2, 13, 13);
        sheet.addMergedRegion(cra8);
        cell14.setCellStyle(headerStyle);

        Cell cell15 = headRow0.createCell(14);
        cell15.setCellValue("台湾");
        CellRangeAddress cra9 = new CellRangeAddress(1, 2, 14, 14);
        sheet.addMergedRegion(cra9);
        cell15.setCellStyle(headerStyle);

        Cell cell16 = headRow0.createCell(15);
        cell16.setCellValue("无户籍");
        CellRangeAddress cra10 = new CellRangeAddress(1, 2, 15, 15);
        sheet.addMergedRegion(cra10);
        cell16.setCellStyle(headerStyle);

        Cell cell17 = headRow0.createCell(16);
        cell17.setCellValue("特殊儿童");
        CellRangeAddress cra11 = new CellRangeAddress(1, 2, 16, 16);
        sheet.addMergedRegion(cra11);
        cell17.setCellStyle(headerStyle);

        Cell cell18 = headRow0.createCell(17);
        cell18.setCellValue("备注");
        CellRangeAddress cra12 = new CellRangeAddress(1, 2, 17, 17);
        sheet.addMergedRegion(cra12);
        cell18.setCellStyle(headerStyle);

    }

}
